03 March 2025

Database Changes for 4.41

This section contains details of database changes between the 4.40 and 4.41 releases.

Table changes:

The following tables have been added.

Table Name Description
HESA_COURSE_ROLES Records organisations that deliver teaching or supervision of research or funding of a course.
HESA_CURRICULUM_ACCREDITATIONS Records the accreditation that a Professional, Statutory or Regulatory Body (PSRB) has given to a course.
HESA_STUDY_LOCATIONS Records the data for the HESA study locations.
HESA_SUPERVISOR_ALLOCATIONS Records the data for the HESA supervisor allocations.
IDP_ALN Holds the additional learning need records.
IDP_ALP Holds the additional learning provision records.
IDP_ALP_DETAIL Holds the additional learning provision records.
IDP_IMPORT_XML Holds the XML string of the import file for IDP.
IDP_NAMED_ESTABLISHMENT Named establishment records for individual development plan (IDP).
IDP_NHS_PROVISION Holds the learner individual development plan NHS provision records.
IDP_PREVIOUS_ESTABLISHMENT Holds the learner individual development plan previous establishment records.
IDP_RESPONSIBILITY Responsibility records for individual development plan (IDP).
IDP_REVIEW Holds the learner individual development plan records.
ILP_INSTANCE_UIOS UIO and cohort date range which applies to the related ILP Instance for the learner
MODULE_COST_CENTRES Records the cost centre(s) for a module.
MODULE_DELIVERY_ROLES Records the delivery organisation(s) for a module.
MODULE_SUBJECTS Records the subject or subjects of a module
PEOPLE_IDP Learner details that are specific to their Individual Development Plan (IDP).
PUS_HESA_ACCREDITATIONS Accreditations for HESA-specific enrolments.
UI_HESA_COURSE_INITIATIVE Stores unbounded HESA course initiatives.

The following columns have been added.

Table Name Column Name Type (Size) Nullable Description
COHORT_FILTERS OPERATOR nvarchar(10) Y Selected operator to be used when evaluating this filter (e.g. EQ, GTE, LTE).
FILTER_DEFINITIONS DATA_SOURCE nvarchar(10) Y Source of available values for this filter (e.g. VERIFIER, PROC).
FILTER_DEFINITIONS DATA_NAME nvarchar(40) Y Optional name of the verifier or procedure to call to retrieve filter values (e.g. GENDER, ETHNICITY).
FILTER_DEFINITIONS LEARNER_DATA_SOURCE nvarchar(128) Y Source view name containing the learner data for this filter.
FILTER_DEFINITIONS LEARNER_DATA_FIELD nvarchar(128) Y Field name in the Source view containing the learner data for this filter.
FILTER_DEFINITIONS LEARNER_DATA_TYPE nvarchar(10) Y The data type of the field containing the learner data (e.g. NUMBER, STRING).
FILTER_DEFINITIONS USE_LEFT_JOIN nvarchar(1) Y Specifies whether learner data from this view should use a left join (Y/N).
ILP_DEF_INSTANCE_TARGETS NAME nvarchar(140) Y The name of the ILP definition instance target.
ILP_DEFINITION_INSTANCES IS_COURSE_ILP nvarchar(1) N Specifies whether this ILP Definition Instance to be used to create course level learner ILPs (Y/N) [DEFAULT=N].
ILP_INSTANCES IS_COURSE_ILP nvarchar(1) Y Specifies whether this ILP instance is a course level ILP (Y/N).
ILP_INSTANCES UIO_ID nvarchar(10,0) Y The optional id of any unit instance occurrence associated with a course level learner ILP [FK=UNIT_INSTANCE_OCCURRENCES.UIO_ID].
PEOPLE_HESA SOC2020 nvarchar(4) Y The standard occupational classification (SOC2020) code for the student if aged 21 or over at the start of their course, or parental occupation code if the student is under 21 [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=SOC2020)].
PEOPLE_PEOPLE_LINKS COMMUNICATION_PREFERENCE nvarchar(100) Y The communication requirements and preferences.
PEOPLE_UNITS_SPECIAL_HESA PREPFLAG nvarchar(2) Y Indicates that the student course session represents a preparatory phase of the students studies [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=PREPFLAG)].
PEOPLE_UNITS_SPECIAL_HESA PLACEMENT nvarchar(7) Y records where the student course session contains a placement [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=PLACEMENT)].
PEOPLE_UNITS_SPECIAL_HESA EMPLOYINGSCHOOL nvarchar(7) Y Collects the employing school of each student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=EMPLOYINGSCHOOL)].
PEOPLE_UNITS_SPECIAL_HESA ENTRYRTE nvarchar(2) Y Describes the route by which the student has accessed initial teacher training (ITT) provision [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=ENTRYRTE)].
PEOPLE_UNITS_SPECIAL_HESA FEEELIG nvarchar(2) Y Distinguishes between those students who are eligible to pay home fees and those who are not [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=FEEELIG)].
PEOPLE_UNITS_SPECIAL_HESA FEESTATUS nvarchar(2) Y Records the fee status of students [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=FEESTATUS)].
PEOPLE_UNITS_SPECIAL_HESA NHSEMP nvarchar(5) Y Identifies the national health service (NHS) employer of students on post-registration health and social care courses [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=NHSEMP)].
PEOPLE_UNITS_SPECIAL_HESA QTS nvarchar(2) Y Indicates whether a student has qualified teacher status [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=QTS)].
PEOPLE_UNITS_SPECIAL_HESA RCSTDNT nvarchar(4) Y Records whether the engagement has been funded in whole or in part by a research council [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=RCSTDNT)].
PEOPLE_UNITS_SPECIAL_HESA RCSTDID nvarchar(8) Y Records the students research council student identifier.
PEOPLE_UNITS_SPECIAL_HESA TRN nvarchar(9) Y Holds the reference number allocated to each student prior to course completion for the training of teachers.
PEOPLE_UNITS_SPECIAL_HESA COLPROVTYPEID nvarchar(2) Y Records the type of collaborative provision [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=COLPROVTYPEID)].
PEOPLE_UNITS_SPECIAL_HESA PARTNERNUMHUS nvarchar(50) Y Records the NUMHUS of the Engagement at the previous provider, where a student has transferred into the returning provider as part of a sequential doctoral research training programme.
PEOPLE_UNITS_SPECIAL_HESA PARTNERSID nvarchar(17) Y Records the SID of the student at the previous provider, where a student has transferred into the returning provider as part of a sequential doctoral research training programme.
PEOPLE_UNITS_SPECIAL_HESA INTENDEDDESTINATION nvarchar(8) Y Records the provider which a leaver is intending to transfer to when they are transferring out as part of a sequential collaboration arrangement for doctoral research training.
PEOPLE_UNITS_SPECIAL_HESA THESISTITLE nvarchar(500) Y Records the title of the thesis which was presented for examination
PEOPLE_UNITS_SPECIAL_HESA PARTNERUKPRN nvarchar(8) Y Records the UKPRN of the provider that previously returned the student, where a student has transferred into the returning provider as part of a sequential doctoral research training programme.
PEOPLE_UNITS_SPECIAL_HESA LEADSCHOOL nvarchar(6) Y Records the unique reference number (URN) of the lead school for school direct students in England [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=LEADSCHOOL)].
PEOPLE_UNITS_SPECIAL_HESA NONREGFEE nvarchar(1) N Identifies enrolments not subject to regulated fees (Y/N) [DEFAULT=N].
PEOPLE_UNITS_SPECIAL_HESA APEL nvarchar(2) Y Indicates if the module was taken through Accreditation of Prior Experiential Learning (APEL) [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=APEL)].
PEOPLE_UNITS_SPECIAL_HESA CONTINUING nvarchar(1) N Indicates where a module is being continued from a previous student course session (Y/N) [DEFAULT=N].
PEOPLE_UNITS_SPECIAL_HESA INACTIVEMOD nvarchar(1) N Records if the module instance has no activity associated with it that would contribute to a students Full Time Equivalence (FTE) (Y/N) [DEFAULT=N].
PEOPLE_UNITS_SPECIAL_HESA MODCOUNT nvarchar(1) Y Records whether the module is countable under Higher Education Funding Council for Wales (HEFCW) rules (Y/N).
PEOPLE_UNITS_SPECIAL_HESA MODULEOUTCOME nvarchar(2) Y Records if the student completed the module in accordance with relevant funding rules [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=MODULEOUTCOME)].
PEOPLE_UNITS_SPECIAL_HESA MODULERESULT nvarchar(2) Y Indicates the academic outcome of the module - i.e. whether the module has been passed or failed [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=MODULERESULT)].
SFG_RECORD_DATA CAN_LEARNER_VIEW nvarchar(1) N Indicates whether the learner can view the specific record data (Y/N) [DEFAULT=Y].
UI_HESA CRDTPTS nvarchar(3) Y Indicates the number of credit points a student gains on successful completion of the module.
UI_HESA CRDTSCM nvarchar(2) Y Describes a credit point transfer scheme to which the module belongs [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=CRDTSCM)].
UI_HESA FTE nvarchar(4,1) Y Indicates the proportion of a full-time equivalent year attributable to the module.
UI_HESA LEVLPTS nvarchar(2) Y Indicates the level of the credit points recorded in module CRDTPTS(which is the module that indicates the number of credit points a student gains on successful completion of the module) [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=LEVLPTS)].
UI_HESA MODLANGID nvarchar(2) Y Records the language in which the module content is available, at least in part [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=MODLANGID)].
UI_HESA QUALITT nvarchar(6) Y Identifies the qualification IT specialism for inclusion in the HESA data futures return.
UI_HESA_DELIVORGANISATIONS ROLETYPE nvarchar(3) Y Records the role played in the course by the organisation [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=ROLETYPE)].

The following columns have been changed.

Table Name Column Name Type (Size) Nullable Previous Values Type (Size) [Nullable]
ATTAINMENTS MAIN_SUBJECT_2 nvarchar(6) Y nvarchar(4) [Y]
EBS_AUTH_PARAMS CLIENT_CREDENTIALS nvarchar(255) Y nvarchar(4) [Y]
FILTER_DEFINITIONS EXPRESSION nvarchar(500) Y nvarchar(100) [Y]
GRADUATE_OUTCOMES_SURVEY NUMHUS nvarchar(50) Y nvarchar(200) [Y]
LEARNER_HE NUMHUS nvarchar(50) Y nvarchar(20) [Y]
LEARNER_HE_DEFAULTS NUMHUS nvarchar(50) Y nvarchar(20) [Y]
PEOPLE_UNITS_SPECIAL_HESA EXCHIND nvarchar(2) Y nvarchar(1) [Y]
PEOPLE_UNITS_SPECIAL_HESA INITIATIVE_1 nvarchar(3) Y nvarchar(2) [Y]
PEOPLE_UNITS_SPECIAL_HESA INITIATIVE_2 nvarchar(3) Y nvarchar(2) [Y]
PEOPLE_UNITS_SPECIAL_HESA INITIATIVE_3 nvarchar(3) Y nvarchar(2) [Y]
PEOPLE_UNITS_SPECIAL_HESA LOCSDY nvarchar(2) Y nvarchar(1) [Y]
PEOPLE_UNITS_SPECIAL_HESA MSTUFEE nvarchar(4) Y nvarchar(2) [Y]
PEOPLE_UNITS_SPECIAL_ILR_HE NUMHUS nvarchar(50) Y nvarchar(20) [Y]
STUDENT_QUAL_AIMS_HESA NUMHUS nvarchar(50) Y nvarchar(20) [Y]
UI_HESA COURSEAIM nvarchar(5) Y nvarchar(3) [Y]
UIO_HESA LOCSDY nvarchar(2) Y nvarchar(1) [Y]
UIO_HESA MSTUFEE nvarchar(4) Y nvarchar(2) [Y]
WEB_CONFIG PARAMETER_VALUE nvarchar(1000) Y nvarchar(255) [Y]
WEB_CONFIG_AUDIT PARAMETER_VALUE_NEW nvarchar(1000) Y nvarchar(255) [Y]
WEB_CONFIG_AUDIT PARAMETER_VALUE_OLD nvarchar(1000) Y nvarchar(255) [Y]

View Changes

The following views have been added:

  • EBS_IDP_CONTACTS

  • EBS_ILP_COHORT_LEARNER_AGE

  • EBS_ILP_COHORT_LEARNER_DISABILITIES

  • EBS_ILP_COHORT_LEARNER_INFO

  • EBS_ILP_COHORT_LEARNER_UIO

  • EBS_ILP_REVIEW_ANSWERS

  • EBS_ILP_REVIEW_QUESTIONS

  • EBS_PROGRESS_CODES_APP_NOT_ACTIVE

  • EBS_SDR_COURSE_COMPLETION_2023

  • EBS_SDR_COURSE_ENROLMENTS_2023

  • EBS_SDR_COURSE_REG_BASE_2023

  • EBS_SDR_COURSE_REGISTER_2023

  • EBS_SDR_QUAL_COMPLETIONS_2023

  • EBS_SDR_SETTINGS_2023

  • EBS_SDR_STUDENTS_2023

  • EBS_SDR_STUDENTS_BASE_2023

  • EBS_SFG_RECORD_DEFINITIONS

  • EBS_UCAS_APPLICANT

  • EBS_UCAS_APPLICANT_CHOICES

  • EBS_UCAS_ATTAINMENTS

  • EBS_UI_HESA_COURSE_INITIATIVE

  • UCAS_HE_DATALOAD

The following columns have been added.

View Name Column Name
EBS_EXAM_ENTRIES TUTORGROUPLIST
EBS_IDP_LEARNERS HOME_ADDRESS_LINE_1
EBS_IDP_LEARNERS HOME_ADDRESS_LINE_2
EBS_IDP_LEARNERS HOME_ADDRESS_LINE_3
EBS_IDP_LEARNERS PEOPLE_IDP_ID
EBS_IDP_LEARNERS PREFERREDNAME
EBS_IDP_LEARNERS REGION
EBS_IDP_LEARNERS START_DATE
EBS_IDP_LEARNERS TELEPHONE_NUMBER
EBS_IDP_LEARNERS TOWN
EBS_LEARNER_CONTACTS_SEARCH COMMUNICATION_PREFERENCE
EBS_PEOPLE_PEOPLE_LINKS COMMUNICATION_PREFERENCE
EBS_PEOPLE_PEOPLE_LINKS_RCMS COMMUNICATION_PREFERENCE

The following columns have been dropped.

Table Name Description
EBS_IDP_LEARNERS HOME_ADDRESS_1
EBS_IDP_LEARNERS HOME_ADDRESS_2
EBS_IDP_LEARNERS HOME_ADDRESS_3
EBS_IDP_LEARNERS HOME_ADDRESS_4
EBS_IDP_LEARNERS HOME_ADDRESS_5
EBS_IDP_LEARNERS PREFERRED_NAME
EBS_IDP_LEARNERS TELEPHONENO

Service Pack 1 Changes

The following columns have been added.

Table Name Column Name Type (Size) Nullable Description  
UI_HESA PREREQUISITE nvarchar(2) Y Records the level of the Course in relation to the qualifications that students are normally required to hold in order to gain entry to the Course  
PEOPLE_UNITS_SPECIAL_HESA APEL nvarchar(2) Y Indicates if the module was taken through Accreditation of Prior Experiential Learning (APEL)  
PEOPLE_UNITS_SPECIAL_HESA CONTINUING nvarchar(1) N Indicates where a module is being continued from a previous StudentCourseSession  
PEOPLE_UNITS_SPECIAL_HESA INACTIVEMOD nvarchar(1) N Records if the ModuleInstance has no activity associated with it that would contribute to a student's FTE  
PEOPLE_UNITS_SPECIAL_HESA MODCOUNT nvarchar(1) Y Records whether the Module is countable under HEFCW rules.  
PEOPLE_UNITS_SPECIAL_HESA MODULEOUTCOME nvarchar(2) Y Records if the student completed the Module in accordance with relevant funding rules.  
PEOPLE_UNITS_SPECIAL_HESA MODULERESULT nvarchar(2) Y Indicates the academic outcome of the Module - i.e. whether the Module has been passed or failed.  

The following columns have been dropped.

Table Name Description
EBS_IDP_LEARNERS HOME_ADDRESS_1
EBS_UNIT_INSTANCES BITTM
EBS_UNIT_INSTANCES SANDWICH
EBS_UNIT_INSTANCES CRDTPTS
EBS_UNIT_INSTANCES CRDTSCM
EBS_UNIT_INSTANCES FTE
EBS_UNIT_INSTANCES LEVLPTS
EBS_UNIT_INSTANCES MODLANGID
EBS_UNIT_INSTANCES COURSEAIM
EBS_UNIT_INSTANCES QUALITT
EBS_UIO CLSDCRS
EBS_UIO FULLYFLEX

The following columns have been changed.

Table Name Column Name Type (Size) Nullable Previous Values Type (Size) [Nullable]
UIO_HESA FULLYFLEX nvarchar(2) Y nvarchar(1) [Y]

Service Pack 3 Changes

The following columns have been added.

Table Name Column Name Type (Size) Nullable Description
PEOPLE_UNITS_SPECIAL_HESA UCASSCHEMECODE nvarchar(4) Y The 4-character scheme code issued by UCAS
PEOPLE_UNITS_SPECIAL_HESA FUNDLENGTH nvarchar(2) Y Indicates the length of the student course session for funding purposes
UIO_HESA FUNDLENGTH nvarchar(2) Y Indicates the length of the student course session for funding purposes

The following columns have been changed.

Table Name Column Name Type (Size) Nullable Previous Values Type (Size) [Nullable]
PEOPLE_UNITS_SPECIAL_HESA FUNDCODE nvarchar(4) Y nvarchar(1) [Y]